-- FUNCTION: public.gaincode(character varying, character varying, character varying, timestamp with time zone, integer, integer, integer)

-- DROP FUNCTION IF EXISTS public.gaincode(character varying, character varying, character varying, timestamp with time zone, integer, integer, integer);

CREATE OR REPLACE FUNCTION public.gaincode(
	p_bill character varying,
	p_field character varying,
	p_billdate timestamp with time zone,
	p_org_id integer,
	p_type integer  DEFAULT 0)
    RETURNS character varying
    LANGUAGE 'plpgsql'
    COST 100
    VOLATILE PARALLEL UNSAFE
AS $BODY$
declare
    v_numhead character varying;
    v_numfoot integer;
    v_footlenth integer;
    v_code character varying;
    v_cnt integer;
    v_type	character varying;
    v_sql text;
    v_iskeyan integer;
    v_orgunitid integer;
begin  
-----current_date
   select numhead || to_char(p_billdate,numdate) as numhead,numfoot,case when footlenth >8 then 8 else footlenth end
   into v_numhead,v_numfoot,v_footlenth
   from  public.tabletype where code=p_bill and org_id = p_org_id and type= p_type;
      raise notice 'Parameter is p_field: %',p_field;
   v_sql := 'select COALESCE(max(' || p_field || '),''' || v_numhead || ''') from public.' || p_bill || ' where ' || p_field || ' like ''' || upper(v_numhead) || '%'' and org_id = ' || p_org_id ;
   raise notice 'Parameter is v_sql: %',v_sql;
   execute  v_sql into v_code; 
   if v_code = v_numhead then
      select v_numhead || to_char(v_numfoot,'fm' || substr('00000000',1,v_footlenth)) into v_code ;
   else
      select v_numhead || to_char(to_number(substr(v_code,length(v_numhead)+1), '9999999999999999999')+1,'fm' || substr('00000000',1,v_footlenth)) into v_code ;
   end if;
   RETURN upper(v_code)::character varying; 
END;  
$BODY$;
ALTER FUNCTION public.gaincode(character varying, character varying, timestamp with time zone, integer, integer)
    OWNER TO postgres;
